package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.*;
import com.rockcent.common.domain.enumclass.Whether;
import com.rockcent.common.utils.RDateUtils;
import com.rockcent.mall.lib.domain.IntegrationCashCouponWallet;
import com.rockcent.mall.lib.dto.IntegrationCashCouponWalletDto;
import com.rockcent.mall.lib.dto.IntegrationChannelDto;
import com.rockcent.mall.lib.dto.IntegrationCouponDetailRespDto;
import com.rockcent.mall.lib.dto.IntegrationCouponRespDto;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

/**
 * Created by zhangjh on 2016/12/14.
 */
@Repository
public class IntegrationCashCouponWalletRepositoryImpl implements IntegrationCashCouponWalletRepositoryCustom {

    private static final Logger log = LoggerFactory.getLogger(IntegrationCashCouponWalletRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;


    @Autowired
    private ObjectMapper objectMapper;

    /**
     * @param useNum
     * @param customerId
     * @return
     */
    @Override
    public Long queryCashWalletCount(Long useNum, Long customerId) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("select %s FROM intergration_cash_wallet f");
            sql.append("select %s FROM integration_cash_coupon_wallet f WHERE is_delete='NO' and f.receive_num=1 and f.exchange_num=1 and f.use_num = :useNum AND f.customer_id = :customerId ");
            params.put("useNum", useNum);
            params.put("customerId", customerId);
            fields.append(" count(1) ");
//            log.info("======================>my sql: "+sql.toString());
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param useNum
     * @param customerId
     * @param pageResult
     * @return
     */
    @Override
    public PageResult<IntegrationCashCouponWallet> queryCashWalletListByUseNumAndCustomerId(Long useNum, Long customerId, PageResult pageResult) {

//        log.info("======================>useNum:" + useNum);
//        log.info("======================>customerId:" + customerId);

        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("SELECT %s FROM intergration_cash_wallet f ");
            sql.append("SELECT %s FROM integration_cash_coupon_wallet f WHERE is_delete='NO' and f.use_num = :useNum AND f.customer_id = :customerId order by f.use_date desc");
            params.put("useNum", useNum);
            params.put("customerId", customerId);
            fields.append(" f.* ");
//            log.info("======================>my sql: "+sql.toString());
        };
        return doing.go(pageResult, IntegrationCashCouponWallet.class, objectMapper, jdbcTemplate, log);


    }

    /**
     * @param discountAmount
     * @param userId
     * @param mallId
     * @return
     */
    @Override
    public IntegrationCashCouponWallet getCloseToCashCoupon(BigDecimal discountAmount, Long userId, Long mallId) {

        SQLObjectDoing<IntegrationCashCouponWallet> result = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("select %s FROM intergration_cash_wallet f");
            sql.append("select %s from integration_cash_coupon_wallet f WHERE is_delete='NO' and exchange_num=1 and receive_num=1 and use_num=0 ");
            sql.append(" and  f.customer_id = :userId");
            sql.append(" and  f.mall_id = :mallId");
            sql.append(" order by ABS(face_value-:discountAmount) asc");
            sql.append(" limit 1");
            params.put("userId", userId);
            params.put("mallId", mallId);
            params.put("discountAmount", discountAmount);
            fields.append(" f.* ");
//            log.info("======================>my sql: "+sql.toString());
        };
        return result.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);

    }

    /**
     * @param cashCouponId
     * @param integrationChannelId
     * @param mallId
     * @param batchPutNo
     * @return
     */
    @Override
    public IntegrationCashCouponWallet updateIntegrationChannelId(Long cashCouponId, Long integrationChannelId, Long mallId, Long batchPutNo) {
        SQLUpdateDoing<IntegrationCashCouponWallet> result = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("select %s FROM intergration_cash_wallet f");
            sql.append("update integration_cash_coupon_wallet set integration_channel_id =:integrationChannelId , batch_put_no = :batchPutNo where  cash_coupon_id = :cashCouponId and mall_id = :mallId");
            params.put("integrationChannelId", integrationChannelId);
            params.put("cashCouponId", cashCouponId);
            params.put("mallId", mallId);
            params.put("batchPutNo", batchPutNo);
        };
        return result.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * @param cashCouponId
     * @param mallId
     */
    @Override
    public void deleteByCashCouponId(Long cashCouponId, Long mallId) {
        SQLUpdateDoing<IntegrationCashCouponWallet> result = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("select %s FROM intergration_cash_wallet f");
            sql.append("update integration_cash_coupon_wallet set is_delete ='YES' where exchange_num=0 and  cash_coupon_id = :cashCouponId and mall_id = :mallId");
            params.put("cashCouponId", cashCouponId);
            params.put("mallId", mallId);
        };
        result.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 统计渠道、现金券的使用情况
     *
     * @param cashCouponId
     * @param integrationChannelId
     * @param mallId
     * @return
     */
    @Override
    public IntegrationCashCouponWallet caculateCash(Long cashCouponId, Long integrationChannelId, Long mallId) {
        SQLObjectDoing<IntegrationCashCouponWallet> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from integration_cash_coupon_wallet f WHERE  is_delete='NO'     and mall_id =:mallId");
            params.put("mallId", mallId);
            if (cashCouponId != null) {
                sql.append(" and cash_coupon_id = :cashCouponId");
                params.put("cashCouponId", cashCouponId);
            }
            if (null != integrationChannelId) {
                sql.append(" and integration_channel_id = :integrationChannelId");
                params.put("integrationChannelId", integrationChannelId);
            }

            fields.append(" if(sum(receive_num) is null,0,sum(receive_num)) as receive_num, if(sum(exchange_num) is null,0,sum(exchange_num)) as exchange_num,if(sum(use_num) is null,0,sum(use_num)) as use_num ");
//            log.info("======================>my sql: "+sql.toString());
        };
        return result.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param dto
     * @param mallId
     * @param whether
     * @param pageResult
     * @return
     */
    @Override
    public PageResult<IntegrationCashCouponWalletDto> findChannelCashDetail(IntegrationChannelDto dto, Long mallId, Whether whether, PageResult pageResult) {

        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
//            sql.append("SELECT %s FROM intergration_cash_wallet f ");
            sql.append("SELECT %s FROM integration_cash_coupon_wallet f left join mall_cash_coupon n on f.cash_coupon_id = n.id WHERE f.mall_id = :mallId");
            Long faceValue = dto.getFaceValue();
            String exchangeCode = dto.getExchangeCode();
            Integer batchNo = dto.getBatchPutNo();
            String startTime = dto.getStartTime();
            String endTime = dto.getEndTime();
            Long integrationChannelId = dto.getId();
            String flagCode = dto.getFlagCode();
            params.put("mallId", mallId);
            if (null != faceValue) {
                sql.append(" and f.face_value = :faceValue");
                params.put("faceValue", faceValue);
            }
            if (StringUtils.isNotBlank(exchangeCode)) {
                sql.append(" and f.exchange_code = :exchangeCode");
                params.put("exchangeCode", exchangeCode);
            }
            if (StringUtils.isNotBlank(flagCode)) {
                sql.append(" and f.flag_code = :flagCode");
                params.put("flagCode", flagCode);
            }
            if (null != batchNo) {
                sql.append(" and f.batch_put_no = :batchNo");
                params.put("batchNo", batchNo);
            }
            if (null != whether) {
                sql.append(" and f.is_delete = :isDelete");
                params.put("isDelete", whether.toString());
            }
            if (null != integrationChannelId) {
                sql.append(" and f.integration_channel_id = :integrationChannelId");
                params.put("integrationChannelId", integrationChannelId);
            }

            sql.append(" and ((f.exchange_num=1 ");

            if (StringUtils.isBlank(startTime) && StringUtils.isBlank(endTime)) {
                LocalDate today = LocalDate.now();
                startTime = today.minusDays(30).toString();
                endTime = today.toString();
            }

            if (StringUtils.isNotBlank(startTime)) {
                startTime = startTime.substring(0, 10);
                sql.append(" and date_format(f.exchange_date,'%Y-%m-%d') >= :startTime");
                params.put("startTime", startTime);

            }
            if (StringUtils.isNotBlank(endTime)) {
                endTime = endTime.substring(0, 10);
                sql.append(" and date_format(f.exchange_date,'%Y-%m-%d') <= :endTime");
                params.put("endTime", endTime);
            }

//            sql.append(") or exchange_num=0)");
            sql.append(") )");
            sql.append(" order by f.exchange_date desc");
            fields.append(" f.*,n.name ");

        };
        return doing.go(pageResult, IntegrationCashCouponWalletDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * @param dto
     * @param mallId
     * @param whether
     * @return
     */
    @Override
    public Long qrExchangeNumByChannelId(IntegrationChannelDto dto, Long mallId, Whether whether) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s FROM integration_cash_coupon_wallet f WHERE f.mall_id=:mallId and f.is_delete = :whether AND f.integration_channel_id = :id ");
            params.put("mallId", mallId);
            params.put("whether", whether.toString());
            params.put("id", dto.getId());
            fields.append(" sum(exchange_num)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * @param channelId
     * @return
     */
    @Override
    public List<IntegrationCouponRespDto> queryList(Long channelId) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from  integration_cash_coupon_wallet ");
            sql.append(" where integration_channel_id =:channelId ");
            sql.append(" and exchange_num=0 and is_delete='NO' and receive_num=0 and use_num=0 ");
            params.put("channelId", channelId);
            fields.append(" exchange_code, flag_code");
        };
        return doing.go(IntegrationCouponRespDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * @param exchangeCode
     * @return
     */
    @Override
    public IntegrationCashCouponWallet findByExchangeCode(String exchangeCode) {
        SQLObjectDoing<IntegrationCashCouponWallet> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from   integration_cash_coupon_wallet ");
            sql.append(" where is_delete='NO' and exchange_code =:exchangeCode ");
            sql.append(" and exchange_num=0 and receive_num=0 and use_num=0");
            params.put("exchangeCode", exchangeCode);
            fields.append(" * ");
        };
        return doing.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param dto
     * @param mallId
     * @param no
     * @return
     */
    @Override
    public Double qrExchangeNumCashAmountByChannelId(IntegrationChannelDto dto, Long mallId, Whether no) {
        SQLObjectDoing<Double> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s FROM integration_cash_coupon_wallet f WHERE f.mall_id=:mallId and f.is_delete = :whether AND f.integration_channel_id = :id and exchange_num=1");
            String startTime = dto.getStartTime();
            String endTime = dto.getEndTime();
            Long faceValue = dto.getFaceValue();
            String exchangeCode = dto.getExchangeCode();
            Integer batchNo = dto.getBatchPutNo();
            String flagCode = dto.getFlagCode();
            if (StringUtils.isBlank(startTime) && StringUtils.isBlank(endTime)) {
                LocalDate today = LocalDate.now();
                startTime = today.minusDays(30).toString();
                endTime = today.toString();
            }

//            if (StringUtils.isNotBlank(startTime)) {
//                startTime = startTime.substring(0, 10) + " 00:00:00";
//                sql.append(" and date_format(f.exchange_date,'%Y-%m-%d') >= :startTime");
//                params.put("startTime", startTime);
//
//            }
//            if (StringUtils.isNotBlank(endTime)) {
//                endTime = endTime.substring(0, 10) + " 23:59:59";
//                sql.append(" and date_format(f.exchange_date,'%Y-%m-%d') <= :endTime");
//                params.put("endTime", endTime);
//            }

            if (StringUtils.isNotBlank(startTime)) {
                sql.append(" and f.exchange_date > :startTime");
                params.put("startTime", RDateUtils.beforeDateStr(startTime, 0));

            }
            if (StringUtils.isNotBlank(endTime)) {
                sql.append(" and f.exchange_date < :endTime");
                params.put("endTime", RDateUtils.afterDateStr(endTime, 1));
            }


            if (null != faceValue) {
                sql.append(" and f.face_value = :faceValue");
                params.put("faceValue", faceValue);
            }
            if (StringUtils.isNotBlank(exchangeCode)) {
                sql.append(" and f.exchange_code = :exchangeCode");
                params.put("exchangeCode", exchangeCode);
            }
            if (StringUtils.isNotBlank(flagCode)) {
                sql.append(" and f.flag_code = :flagCode");
                params.put("flagCode", flagCode);
            }
            if (null != batchNo) {
                sql.append(" and f.batch_put_no = :batchNo");
                params.put("batchNo", batchNo);
            }
            params.put("mallId", mallId);
            params.put("whether", no.toString());
            params.put("id", dto.getId());
            fields.append(" sum(face_value) ");
        };
        return result.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<IntegrationCouponDetailRespDto> findByCashCounponId(Long cashCouponId) {

        SQLListDoing<IntegrationCouponDetailRespDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from  integration_cash_coupon_wallet ");
            sql.append(" where cash_coupon_id =:cashCouponId ");
            sql.append(" and exchange_num=0 and is_delete='NO' and receive_num=0 and use_num=0");
            params.put("cashCouponId", cashCouponId);
            fields.append(" exchange_code,flag_code");
        };
        return doing.go(IntegrationCouponDetailRespDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public IntegrationCashCouponWallet findByExchangeCodeAndIntegrationChannelIdAndIsDelete(String exchangeCode, Long integrationChannelId, Whether whether) {


        SQLObjectDoing<IntegrationCashCouponWallet> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from   integration_cash_coupon_wallet ");
            sql.append(" where is_delete=:whether and exchange_code =:exchangeCode ");
            sql.append(" and receive_num=0 and use_num=0");
            params.put("exchangeCode", exchangeCode);
            params.put("whether", whether.toString());
            fields.append(" * ");
        };
        return doing.go(IntegrationCashCouponWallet.class, jdbcTemplate, log, objectMapper);

    }

    /**
     * 领取数量
     * @param mallId
     * @param cashCouponId
     * @return
     * @throws Exception
     */
    @Override
    public Long queryCashWalletReceiveCount(Long mallId, Long cashCouponId) throws Exception {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s FROM integration_cash_coupon_wallet f WHERE f.mall_id=:mallId and f.cash_coupon_id=:cashCouponId");
            params.put("mallId", mallId);
            params.put("cashCouponId", cashCouponId);
            fields.append(" sum(f.receive_num) ");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 使用数量
     * @param mallId
     * @param cashCouponId
     * @return
     * @throws Exception
     */
    @Override
    public Long queryCashWalletUseCount(Long mallId, Long cashCouponId) throws Exception {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s FROM integration_cash_coupon_wallet WHERE mall_id=:mallId and cash_coupon_id=:cashCouponId");
            params.put("mallId", mallId);
            params.put("cashCouponId", cashCouponId);
            fields.append(" sum(use_num) ");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }
}
